<?php
class MStock extends CI_Model{

	public function __construct()
    {
        parent::__construct();
    }
    
    function getItemByStockIdItemId($id, $itemId){
	    $data = array();
	    $this->db->where('stock_id',id_clean($id));
        $this->db->where('item_id',id_clean($itemId));
	    $this->db->limit(1);
	    $Q = $this->db->get('item');
	    if ($Q->num_rows() > 0){
	      $data = $Q->row_array();
	    }
	    $Q->free_result();    
	    return $data;    
	 }
	 
     function getStockInfo()
     {
        //$sql = "SELECT count(status_id) as cnt, status_id FROM `stock` WHERE createdby='".$_SESSION['username']."' AND branch_id='".$_SESSION['branch']."' GROUP BY status_id";
        $sql = "SELECT count(status_id) as cnt, status_id FROM `stock` GROUP BY status_id";
        $query = $this->db->query($sql);
        return $query->result();
     }
     
     function getPurchasedList($item = '', $datepurchase = '')
     {
         $this->db->select("stock.stock_id, stock.item, stock.code, stock.ref, stock.unit, stock.cpu, stock.total,stock.datepurchased,status.status, stock.remark");
	     $this->db->from('stock');
         $this->db->join('status','status.status_id = stock.status_id', 'LEFT');
         $this->db->join('item','item.stock_id = stock.stock_id', 'LEFT');
         $this->db->join('broken','broken.stock_id = stock.stock_id', 'LEFT');
         $this->db->where('stock.category_id',2);
  
         $this->db->where('stock.branch_id', $_SESSION['branch']);
         
         if($item != '' && $item != null)
         {
            $this->db->where('stock.item LIKE', '%'.$item.'%');
         }
         if($datepurchase != '' && $datepurchase != null)
         {
            $this->db->where('DATE(stock.datepurchased)', date("Y-m-d", strtotime($datepurchase)));
         }
	     $this->db->order_by("stock.item");
         $query = $this->db->get();
         return $query->result();
     }
     
     function getReportPrinting($cat, $start = '', $end = '', $branch = '', $type = '')
     {
         $br = '';
         if($branch != '' && $branch != null && $branch != '00HO00')
         { 
            $br = db_clean($branch);
         }else{
            $br = $_SESSION['branch'];
         }
        $sql = "CALL `sp_report`('".$cat."','".$start."','".$end."','".$br."','".$type."')";
        return $this->db->query($sql); 
        
     }
     
     function generateReportByType($sql)
     {
          
     }
     
/*----Stationery-----*/
    function getStationeryList($item = '', $datepurchase = ''){
         $sql = "SELECT `stock`.`stock_id` , `stock`.`item` , `stock`.`code`,\n";
         $sql .= "(stock.unit - (SUM(COALESCE(item.unit,0)) + SUM(COALESCE(broken.unit,0)))) AS unit, `stock`.`cpu`,\n";
         $sql .= "(stock.total - (SUM(COALESCE(item.total,0)) + SUM(COALESCE(broken.total,0)))) AS total,\n"; 
         $sql .= "`stock`.`datepurchased`, `stock`.`remark` FROM `stock`\n";
         $sql .= "LEFT JOIN `broken` ON `broken`.`stock_id` = `stock`.`stock_id`\n";
         $sql .= "LEFT JOIN `item` ON `item`.`stock_id` = `stock`.`stock_id`\n";
         $sql .= "WHERE `stock`.`category_id`=1\n";
    
         $sql .= "AND `stock`.`branch_id`='".$_SESSION['branch']."'\n";
         
         if($item != '' && $item != null)
         {
            $sql .= "AND `stock`.`item` LIKE '".$item."%'\n";
         }
         if($datepurchase != '' && $datepurchase != null)
         {
            $datepurchase = date("Y-m-d", strtotime($datepurchase));
            $sql .= "AND DATE(`stock`.`datepurchased`)='".$datepurchase."'\n";
         }
         $sql .= "GROUP BY `stock`.`stock_id`\n";
         $sql .= "ORDER BY `stock`.`item`";
         $query = $this->db->query($sql);
         return $query->result();
	 }
     
     function getStationeryUsed($start = '', $end = '', $branch = '', $exp = false){
         if($exp)
            $this->db->select("item.dateused, stock.item, stock.code, item.unit, item.cpu, item.total, item.ref, status.status, branch.branch, location.location, item.staff, item.note As remark");
         else
            $this->db->select("item.stock_id, item.item_id, item.dateused, stock.item, stock.code, item.unit, item.cpu, item.total, item.ref, status.status, branch.branch, location.location, item.staff,item.note As remark");
               
	     $this->db->from('item');
         $this->db->join('stock','item.stock_id = stock.stock_id');
         $this->db->join('status','item.status_id = status.status_id', 'LEFT');
         $this->db->join('branch','item.branch_id = branch.branch_id', 'LEFT');
         $this->db->join('location','item.location_id = location.location_id', 'LEFT');
         $this->db->where('stock.category_id',1);
         
         $this->db->where('stock.branch_id', $_SESSION['branch']);
         if($start != '' && $start != null){
            $this->db->where('DATE(item.dateused) >=', date("Y-m-d", strtotime($start)));
         }
         if($end != '' && $end != null){
            $this->db->where('DATE(item.dateused) <=', date("Y-m-d", strtotime($end)));
         }
         if($branch != '' && $branch != null)
         {
            $this->db->where('item.branch_id', db_clean($branch));
         }
         $this->db->order_by("branch.branch");
         $query = $this->db->get();
         //return $query->result();
         return $query;
	 }
     
     function getStationeryBroken($start = '', $end = '', $branch = '', $exp = false){
         if($exp)
            $this->db->select("stock.datepurchased, stock.item, broken.unit, broken.cpu, broken.total, broken.note As remark");
         else
            $this->db->select("broken.broken_id, stock.datepurchased, stock.item, broken.unit, broken.cpu, broken.total, broken.note As remark");
            
	     $this->db->from('broken');
         $this->db->join('stock','broken.stock_id = stock.stock_id');
         $this->db->where('stock.category_id',1);
        
         $this->db->where('stock.branch_id', $_SESSION['branch']);
         if($start != '' && $start != null){
            $this->db->where('DATE(stock.datepurchased) >=', date("Y-m-d", strtotime($start)));
         }
         if($end != '' && $end != null){
            $this->db->where('DATE(stock.datepurchased) <=', date("Y-m-d", strtotime($end)));
         }
         if($branch != '' && $branch != null)
         {
            $this->db->where('stock.branch_id', db_clean($branch));
         }
         $this->db->order_by("stock.item");
         $query = $this->db->get();
         //return $query->result();
         return $query;
	 }
/*----End Stationery--------*/

/*----FA & NA-----*/
    function getAssetList($item = '', $datepurchase = '', $cat=''){
         $sql = "SELECT `stock`.`stock_id` , `stock`.`item` , `stock`.`code`,\n";
         $sql .= "(stock.unit - (SUM(COALESCE(item.unit,0)) + SUM(COALESCE(broken.unit,0)))) AS unit, `stock`.`cpu`,\n";
         $sql .= "(stock.total - (SUM(COALESCE(item.total,0)) + SUM(COALESCE(broken.total,0)))) AS total,\n"; 
         $sql .= "`stock`.`datepurchased`, `stock`.`remark` FROM `stock`\n";
         $sql .= "LEFT JOIN `broken` ON `broken`.`stock_id` = `stock`.`stock_id`\n";
         $sql .= "LEFT JOIN `item` ON `item`.`stock_id` = `stock`.`stock_id`\n";
         $sql .= "WHERE `stock`.`category_id`=".$cat."\n";
        
         $sql .= "AND `stock`.`branch_id`='".$_SESSION['branch']."'\n";
         if($item != '' && $item != null)
         {
            $sql .= "AND `stock`.`item` LIKE '".$item."%'\n";
         }
         if($datepurchase != '' && $datepurchase != null)
         {
            $datepurchase = date("Y-m-d", strtotime($datepurchase));
            $sql .= "AND DATE(`stock`.`datepurchased`)='".$datepurchase."'\n";
         }
         $sql .= "GROUP BY `stock`.`stock_id`\n";
         $sql .= "ORDER BY `stock`.`item`";
         $query = $this->db->query($sql);
         return $query->result();
	 }
     
     function getAssetUsed($cat='', $start = '', $end = '', $branch = '', $exp = false){
         $sql = null;
         if($exp)
            $sql = "item.dateused, stock.item, stock.code, item.unit, item.cpu, item.total, item.ref, status.status, branch.branch, location.location, item.staff, item.note As remark";   
         else
            $sql = "item.stock_id, item.item_id, item.dateused, stock.item, stock.code, item.unit, item.cpu, item.total, item.ref, status.status, branch.branch, location.location, item.staff, item.note As remark";
            
         
         if($cat == 3)
            $sql .= ", fatype.fatype as type";
         elseif($cat == 4)
            $sql .= ", natype.natype as type";
         
         $this->db->select($sql);        
	     $this->db->from('item');
         $this->db->join('stock','item.stock_id = stock.stock_id');
         if($cat == 3)
            $this->db->join('fatype','fatype.fatype_id = stock.fatype_id');
         elseif($cat == 4)
            $this->db->join('natype','natype.natype_id = stock.natype_id');
            
         $this->db->join('status','item.status_id = status.status_id', 'LEFT');
         $this->db->join('branch','item.branch_id = branch.branch_id', 'LEFT');
         $this->db->join('location','item.location_id = location.location_id', 'LEFT');
            
         $this->db->where('stock.category_id',$cat);
     
         $this->db->where('stock.branch_id', $_SESSION['branch']);
         if($start != '' && $start != null){
            $this->db->where('DATE(item.dateused) >=', date("Y-m-d", strtotime($start)));
         }
         if($end != '' && $end != null){
            $this->db->where('DATE(item.dateused) <=', date("Y-m-d", strtotime($end)));
         }
         if($branch != '' && $branch != null)
         {
            $this->db->where('item.branch_id', db_clean($branch));
         }
         $this->db->order_by("branch.branch");
         $query = $this->db->get();
         //return $query->result();
         return $query;
	 }
     
     function getAssetBroken($cat, $start = '', $end = '', $branch = '', $exp = false){
         if($exp)
            $this->db->select("stock.datepurchased, stock.item, broken.unit, broken.cpu, broken.total, broken.note As remark");
	     else
            $this->db->select("broken.broken_id, stock.datepurchased, stock.item, broken.unit, broken.cpu, broken.total, broken.note As remark");
        
         $this->db->from('broken');
         $this->db->join('stock','broken.stock_id = stock.stock_id');
         $this->db->where('stock.category_id',$cat);
         
         $this->db->where('stock.branch_id', $_SESSION['branch']);
         if($start != '' && $start != null){
            $this->db->where('DATE(stock.datepurchased) >=', date("Y-m-d", strtotime($start)));
         }
         if($end != '' && $end != null){
            $this->db->where('DATE(stock.datepurchased) <=', date("Y-m-d", strtotime($end)));
         }
         if($branch != '' && $branch != null)
         {
            $this->db->where('stock.branch_id', db_clean($branch));
         }
         $this->db->order_by("stock.item");
         $query = $this->db->get();
         //return $query->result();
         return $query;
	 }
/*----End FA & NA--------*/
     
	 function getStockList($cat, $item = '', $datepurchase = '', $start = '', $end = '', $branch = ''){
         $sql = "SELECT `stock`.`stock_id` , trim(`stock`.`item`) As `item`, `stock`.`code` , `stock`.`ref` , stock.unit, `stock`.`cpu`,\n";
         $sql .= "(SELECT SUM(COALESCE(unit,0)) FROM item where stock_id=`stock`.stock_id) AS unitused,\n";
         $sql .= "(SELECT SUM(COALESCE(unit,0)) FROM broken where stock_id=`stock`.stock_id) as unitbroken,\n";
         $sql .= " `stock`.`datepurchased` , `stock`.`remark` FROM `stock`\n";
         $sql .= "WHERE `stock`.`category_id`=".$cat."\n";
        
         $sql .= "AND `stock`.`branch_id`='".$_SESSION['branch']."'\n";
         
         if($item != '' && $item != null)
         {
            $sql .= "AND `stock`.`item` LIKE '%".$item."%'\n";
         }
         if($datepurchase != '' && $datepurchase != null)
         {
            $datepurchase = date("Y-m-d", strtotime($datepurchase));
            $sql .= "AND DATE(`stock`.`datepurchased`)='".$datepurchase."'\n";
         }
         $sql .= "GROUP BY `stock`.`stock_id`\n";
         $sql .= "ORDER BY trim(`stock`.`item`)";
         $query = $this->db->query($sql);
         return $query->result();
	 }
     
     function getItemsBroken($start = '', $end = '', $branch = '', $exp = false){
         if($exp)
            $this->db->select("stock.datepurchased, printtype.type, stock.item, broken.unit, broken.cpu, broken.total, broken.refstart, CONCAT('refend', broken.refstart, ',', CAST(broken.refstart AS decimal)+(broken.unit * 50)) AS refend, broken.refall, broken.note As remark", false);
         else
            $this->db->select("broken.broken_id, stock.datepurchased, printtype.type, stock.item, broken.unit, broken.cpu, broken.total, broken.refstart, CONCAT('refend', broken.refstart, ',', CAST(broken.refstart AS decimal) * broken.unit) AS refend, broken.refall, broken.note As remark", false);
            
	     $this->db->from('broken');
         $this->db->join('stock','broken.stock_id = stock.stock_id');
         $this->db->join('printtype','printtype.type_id = stock.type_id', 'LEFT');
         $this->db->where('stock.category_id',2);
        
         $this->db->where('stock.branch_id', $_SESSION['branch']);
         
         if($start != '' && $start != null){
            $this->db->where('DATE(stock.datepurchased) >=', date("Y-m-d", strtotime($start)));
         }
         if($end != '' && $end != null){
            $this->db->where('DATE(stock.datepurchased) <=', date("Y-m-d", strtotime($end)));
         }
         if($branch != '' && $branch != null)
         {
            $this->db->where('stock.branch_id', db_clean($branch));
         }
         $this->db->order_by("stock.item");
         $query = $this->db->get();
         //return $query->result();
         return $query;
	 }
     
     function getItemsUsed($start = '', $end = '', $branch = '', $exp = false, $type = ''){
         if($exp)
            $this->db->select("item.dateused, printtype.type, stock.item, item.unit, item.cpu, item.total, item.ref, CONCAT('refend', item.ref, ',', CAST(item.ref AS decimal)+ (item.unit * 50)) AS refend, status.status, branch.branch, location.location, item.staff,item.note As remark", false);
         else
            $this->db->select("item.stock_id, item.item_id, item.dateused, printtype.type, stock.item, item.unit, item.cpu, item.total, item.ref, status.status, branch.branch, location.location, item.staff, item.note As remark");
               
	     $this->db->from('item');
         $this->db->join('stock','item.stock_id = stock.stock_id');
         $this->db->join('status','item.status_id = status.status_id', 'LEFT');
         $this->db->join('branch','item.branch_id = branch.branch_id', 'LEFT');
         $this->db->join('location','item.location_id = location.location_id', 'LEFT');
         $this->db->join('printtype','printtype.type_id = stock.type_id', 'LEFT');
         $this->db->where('stock.category_id',2);
        
         $this->db->where('stock.branch_id', $_SESSION['branch']);
         if($start != '' && $start != null){
            $this->db->where('DATE(item.dateused) >=', date("Y-m-d", strtotime($start)));
         }
         if($end != '' && $end != null){
            $this->db->where('DATE(item.dateused) <=', date("Y-m-d", strtotime($end)));
         }
         if($branch != '' && $branch != null)
         {
            $this->db->where('item.branch_id', db_clean($branch));
         }
         if($type !='' && $type != null)
         {
            $this->db->where('stock.type_id',id_clean($type));
         }
         $this->db->order_by("branch.branch");
         $query = $this->db->get();
         //return $query->result();
         return $query;
	 }
     
     function getDefaultItemByType($type){
         $this->db->distinct();
         $this->db->select("stock.stock_id, stock.item, stock.code, stock.unit, stock.cpu, stock.total,stock.datepurchased, stock.dateprinted, stock.ref, stock.type_id, category.category, printtype.type");
	     $this->db->from('stock');
         $this->db->join('category','stock.category_id = category.category_id');
         $this->db->join('printtype','printtype.type_id = stock.type_id');
         $this->db->order_by("stock.ref");
         $this->db->where('stock.type_id',$type);
         $this->db->limit(1);
         $query = $this->db->get();
         return $query->result();
	 }
     
     function getStockById($id){
         $data = array();
         $sql = "SELECT `stock`.`stock_id` , `stock`.`item`, `item`.`item_id` , `stock`.`code` , `stock`.`ref`, `stock`.`type_id`,`stock`.`status_id`,`stock`.`activate`,`stock`.`unit` as `bought`, `stock`.`cpu`,`stock`.`assign_id`, `stock`.`fatype_id`,`stock`.`natype_id` ,\n";
         $sql .= "(SELECT SUM(COALESCE(unit,0)) FROM item where stock_id=`stock`.stock_id) AS unitused,\n";
         $sql .= "(SELECT SUM(COALESCE(unit,0)) FROM broken where stock_id=`stock`.stock_id) as unitbroken,\n";
         $sql .= "`stock`.`datepurchased`, `stock`.`remark` FROM (`stock`)\n";
         $sql .= "LEFT JOIN `item` ON `item`.`stock_id` = `stock`.`stock_id`\n";
         $sql .= "WHERE `stock`.`stock_id`=".id_clean($id)." LIMIT 0,1";
         $query = $this->db->query($sql);
         if ($query->num_rows() > 0){
	      $data = $query->row_array();
         }
	     $query->free_result();    
	     return $data;    
	 }
     
     function editDefaultItem($id=0){
        $data = array( 
			'item' => db_clean($_POST['item']),
            'code' => db_clean($_POST['code']),
            'unit' => id_clean($_POST['unit']),
            'cpu' => id_clean($_POST['cpu']),
            'total'=>id_clean($_POST['unit'] * $_POST['cpu']), 
            'ref' => db_clean($_POST['ref']),
            'datepurchased' => db_clean($_POST['datepurchased']),
            'remark' => $_POST['remark'],
			'activate' => db_clean(isset($_POST['activate']) ? 1 : 0)
		);
        $this->db->set('modified', 'NOW()', FALSE);
        $this->db->where('stock_id', $id);
        $this->db->update('stock', $data);    
     }
     
    function generateRefFormat($startRef, $endRef)
    {
        $ref = '';
        if($endRef > 0)
        {
            $endRef = $endRef - 1;
        }
        if(strlen($startRef) > strlen($endRef))
        {
            for($i = 0; $i < (strlen($startRef) - strlen($endRef)); $i++)
            {
                $ref .= '0';
            }
        }
        return $ref.''.$endRef;
    } 
     
    function generateRefNum($row)
    {
        if(is_array($row) && count($row))
        {
            $maxRef = $row['ref'];
            $numRef = id_clean($maxRef);
            $numRef = $numRef > 0 ? $numRef+(50 * id_clean($row['unit']) ): $numRef;
            $ref = '';
            for($i = 0; $i < (strlen($maxRef) - strlen($numRef)); $i++)
            {
                $ref .= '0';
            }
            return $ref.''.$numRef;
        }
        return '';
    }
    
     function getMaxRefNo($type)
     {
        $query = $this->db->query("SELECT ref, unit FROM stock WHERE ref IS NOT NULL AND type_id=".$type." ORDER BY ref DESC LIMIT 0,1");
        $row = $query->row_array();  
        //return (is_array($row) && count($row) ) ? $row['ref'] : 0;
        return $row;
     }
     
     function getPrintTypeById($id)
     {
        $query = $this->db->query("SELECT type FROM printtype WHERE activate=1 AND type_id=".$id." LIMIT 0,1");
        $row = $query->row_array();  
        return (is_array($row) && count($row) ) ? $row[0] : null;
     }
     
     function addEditStock($id=0, $asset = false){
        $type_id = isset($_POST['type_id']) ? id_clean($_POST['type_id']) : null;
		$data = array( 
			'item' => db_clean($_POST['item']),
            'code' => isset($_POST['code']) ? db_clean($_POST['code']) : '',
            'unit' => id_clean($_POST['unit']),
            'cpu' => id_clean($_POST['cpu']),
            'total'=>id_clean($_POST['unit'] * $_POST['cpu']), 
            'category_id' => id_clean($_POST['category_id']),
            'type_id' => $type_id,
            'ref' => isset($_POST['ref']) ? db_clean($_POST['ref']) : null,
            'datepurchased' => db_clean($_POST['datepurchased']),
            'createdby' => db_clean($_SESSION['username']),
            'branch_id' => db_clean($_SESSION['branch']),
            'remark' => $_POST['remark'],
            'fatype_id' => isset($_POST['fatype_id']) ? db_clean($_POST['fatype_id']) : '',
            'natype_id' => isset($_POST['natype_id']) ? db_clean($_POST['natype_id']) : '',
			'activate' => db_clean(isset($_POST['activate']) ? 1 : 0)
		);
        if($id > 0)
        {
            $this->db->set('modified', 'NOW()', FALSE);
            $this->db->set('status_id', id_clean($_POST['status_id']));
            $this->db->where('stock_id', $id);
            $this->db->update('stock', $data);
            
            if(id_clean($_POST['status_id']) == 2 || id_clean($_POST['status_id']) == 4)
            {
                $this->addEditItem($id);
            }
            elseif(id_clean($_POST['status_id']) == 3)
            {
                $this->addEditBroken($id);
            }
            else
            {
                //$this->deleteStockFromItem($id);
                //$this->deleteStockFromBroken($id);
            }
                
        }else{
            //$this->db->set('ref', $ref);
            $this->db->set('created', 'NOW()', FALSE);
            $this->db->insert('stock', $data); 
            if($asset && (id_clean($_POST['status_id']) == 2 || id_clean($_POST['status_id']) == 4))
            {
                $id = $this->db->insert_id();
                $this->addEditItem($id);    
            }  
            
        }	 
            
	 }
     
     function addEditItem($stockId=0, $itemId=0)
     {
        $data = array(
            'stock_id'=>$stockId,
            'dateused'=>db_clean($_POST['dateused']),
            'unit' => isset($_POST['unitbroken']) ? id_clean($_POST['unitused']) - id_clean($_POST['unitbroken']) : id_clean($_POST['unitused']),
            'cpu' => id_clean($_POST['cpu']),
            'total'=>id_clean($_POST['unitused'] * $_POST['cpu']),
            'ref' => isset($_POST['refused']) ? db_clean($_POST['refused']):null, 
            'branch_id'=>db_clean($_POST['branch_id']),
            'location_id'=>isset($_POST['location_id']) ? db_clean($_POST['location_id']) : '',
            'note'=>isset($_POST['note']) ? db_clean($_POST['note']) : null,
            'staff'=>db_clean($_POST['staff'])
        );
        if($itemId > 0)
        {
            if(id_clean($_POST['status_id']) == 3)
            {
                $this->addEditBroken($stockId);
                $this->db->set('status_id', id_clean($_POST['status']));
            }
            elseif($_SESSION['branch']!='00HO00' && id_clean($_POST['status_id']) == 5){
                $this->setStockReturn($stockId,$_POST['branch_id'],$itemId);       
            }
            else{
                $this->db->set('status_id', id_clean($_POST['status_id']));
            }
            $this->db->where('stock_id', $stockId);
            $this->db->where('item_id', $itemId);
            $this->db->update('item', $data);
            if(db_clean($_POST['branch_id']) != '00HO00' && $_SESSION['branch'] == '00HO00')
                $this->addAssignedStock($stockId, $itemId);
        }
        else{
            $this->db->set('status_id', id_clean($_POST['status_id']));
            $this->db->insert('item', $data);
            if(db_clean($_POST['branch_id']) != '00HO00' && $_SESSION['branch'] == '00HO00')
                $this->addAssignedStock(0,$this->db->insert_id());
        }  
     }
     
     function addAssignedStock($id=0, $itemId=0)
     {
        $type_id = isset($_POST['type_id']) ? id_clean($_POST['type_id']) : null;
        if($id > 0 && $itemId > 0){
            $data = array( 
                'unit' => id_clean($_POST['unitused']),
                'cpu' => id_clean($_POST['cpu']),
                'total'=>id_clean($_POST['unitused'] * $_POST['cpu']),
                'ref' => isset($_POST['refused']) ? db_clean($_POST['refused']) : null,
                'branch_id' => db_clean($_POST['branch_id']),
    			'activate' => db_clean(isset($_POST['activate']) ? 1 : 0),
                'datepurchased'=>db_clean($_POST['dateused']),//date assigned
                'assign_id' => $itemId > 0 ? id_clean($itemId) : null
    		);
            //$this->db->set('datepurchased', 'NOW()', FALSE);
            $this->db->set('modified', 'NOW()', FALSE);
            $this->db->where('assign_id', $itemId);
            $this->db->update('stock', $data);
        }
        else{
            $data = array( 
    			'item' => db_clean($_POST['item']),
                'code' => isset($_POST['code']) ? db_clean($_POST['code']) : '',
                'unit' => id_clean($_POST['unitused']),
                'cpu' => id_clean($_POST['cpu']),
                'total'=>id_clean($_POST['unitused'] * $_POST['cpu']), 
                'category_id' => id_clean($_POST['category_id']),
                'type_id' => $type_id,
                'ref' => isset($_POST['refused']) ? db_clean($_POST['refused']) : null,
                'datepurchased'=>db_clean($_POST['dateused']),//date assigned
                //'createdby' => db_clean($_SESSION['username']),
                'branch_id' => db_clean($_POST['branch_id']),
                'remark' => $_POST['remark'],
                'fatype_id' => isset($_POST['fatype_id']) ? db_clean($_POST['fatype_id']) : '',
                'natype_id' => isset($_POST['natype_id']) ? db_clean($_POST['natype_id']) : '',
    			'activate' => db_clean(isset($_POST['activate']) ? 1 : 0),
                'assign_id' => $itemId > 0 ? id_clean($itemId) : null
    		);
            //$this->db->set('datepurchased', 'NOW()', FALSE);
            $this->db->set('created', 'NOW()', FALSE);
            $this->db->insert('stock', $data);
        }  
     }
     
     function addEditBroken($stockId=0)
     {
        $data = array(
            'stock_id'=>$stockId,
            'unit' => isset($_POST['unitbroken']) ? id_clean($_POST['unitbroken']) : 0,
            'cpu' => id_clean($_POST['cpu']),
            'total'=>id_clean($_POST['unitbroken'] * $_POST['cpu']), 
            'refstart' => isset($_POST['refstart']) ? db_clean($_POST['refstart']) : null,
            'note'=>isset($_POST['notebroken']) ? db_clean($_POST['notebroken']) : null,
            'refall' => isset($_POST['refall']) ? db_clean($_POST['refall']) : null
        );
        $this->db->set('datebroken', 'NOW()', FALSE);
        $this->db->insert('broken', $data);   
       
     }
     
     function getStockIdFromItem($id)
     {
        //$this->db->where('stock_id', $id);
        $query = $this->db->query('SELECT stock_id FROM item WHERE stock_id='.$id);
        $row = $query->row_array();
        return (is_array($row) && count($row) ) ? $row['stock_id'] : 0;
     }
	 
     function getStockIdFromBroken($id)
     {
        $query = $this->db->query('SELECT stock_id FROM broken WHERE stock_id='.$id);
        $row = $query->row_array();
        return (is_array($row) && count($row) ) ? $row['stock_id'] : 0;
     }
	  
	 function deleteStock($id){
	 	$this->db->where('stock_id', id_clean($id));
		$this->db->delete('stock');
	 }
     
     function deleteItem($id){
	 	$this->db->where('item_id', id_clean($id));
		$this->db->delete('item');	
	 }
     
     function deleteStockByAssignId($id){ 
	 	$this->db->where('assign_id', id_clean($id));
		$this->db->delete('stock');	
	 }
     
     function deleteBroken($id)
     {
    	$this->db->where('broken_id', id_clean($id));
		$this->db->delete('broken');
     }
     
     function setStatusInStock($id)
     {
        $this->db->set('status_id',1);
        $this->db->where('stock_id', id_clean($id));
        $this->db->update('stock');
     }
     
     function getTotalByCatId($cat)
     {
        $data = array();
	    $this->db->where('category_id',id_clean($cat));
        $this->db->where('branch_id',$_SESSION['branch']);
	    $this->db->limit(1);
	    $Q = $this->db->get('track_total');
	    if ($Q->num_rows() > 0){
	      $data = $Q->row_array();
	    }
	
	    $Q->free_result();    
	    return $data;    
     }
     
     function addEditTotal()
     {
        $type_id = isset($_POST['type_id']) ? id_clean($_POST['type_id']) : null;
        $cat_id = id_clean($_POST['category_id']);
        $data = array(
            'total'=>db_clean($_POST['total']), 
            'category_id'=>$cat_id,
            'type_id'=> $type_id,
            'title'=>db_clean($_POST['title']),
            'branch_id'=>$_SESSION['branch']
        );
        if(count($this->checkExistedTotal($cat_id,$type_id)))
        {
             $this->db->where('category_id', $cat_id);
             $this->db->where('type_id', $type_id);
             $this->db->where('branch_id', $_SESSION['branch']);
             $this->db->update('track_total',$data); 
        }else{
             $this->db->insert('track_total',$data);
        }
     }
     
     function checkExistedTotal($cat, $type_id = null)
     {
         $sql = "SELECT total FROM `track_total` WHERE category_id=".$cat." AND branch_id='".$_SESSION['branch']."'";
         if(!empty($type_id))
         {
            $sql .= " AND type_id=".$type_id;
         }
         $sql . " LIMIT 0,1";
         $query = $this->db->query($sql);
         $row = array();
         if ($query->num_rows() > 0){
	        //return true;
            $row = $query->row_array();
         }
         //return false;
         return $row;
     }    
     
     function checkExistedReturn($itemId, $branch)
     {
         $sql = "SELECT unit,ref FROM `stock_return` WHERE item_id=".$itemId." AND branch_id='".$branch."' LIMIT 0,1";
         $query = $this->db->query($sql);
         $row = array();
         if ($query->num_rows() > 0){
            $row = $query->row_array();
         }
         return $row;
     }
     
     function getTotal($cat, $type_id = null)
     {
         $sql = "SELECT SUM(total) As total FROM `track_total`WHERE category_id=".$cat." AND branch_id='".$_SESSION['branch']."'";
         if(!empty($type_id))
         {
            $sql .= " AND type_id=".$type_id;
         }
         $sql . " LIMIT 0,1";
         $query = $this->db->query($sql);
         $row = array();
         if ($query->num_rows() > 0){
	        //return true;
            $row = $query->row_array();
         }
         //return false;
         return $row;
     }
     
     function setStockReturn($stockId, $branch, $itemId)
     {
        $cnt = count($this->checkExistedReturn($itemId, $branch));
        if($cnt && $cnt>0)
        {
            $sql = "UPDATE `stock_return` SET unit=".id_clean($_POST['unitreturn']).",ref='".db_clean($_POST['refreturn'])."' WHERE item_id=".$itemId." AND branch_id='".$branch."'";
        }else{
            $sql = "INSERT INTO `stock_return` SELECT item.item_id,stock.category_id,stock.code,'".db_clean($_POST['refreturn'])."',stock.item,".id_clean($_POST['unitreturn']).",
                    item.cpu,(item.cpu * ".id_clean($_POST['unitreturn'])."),stock.status_id,stock.remark,stock.type_id,stock.contracttype_id,stock.createdby,'".$branch."',now(),0 As activate,stock.assign_id,
                    stock.fatype_id,stock.natype_id FROM stock INNER JOIN item ON stock.stock_id=item.stock_id WHERE item.stock_id=".$stockId." AND item.item_id=".$itemId;    
        }
        $this->db->query($sql);
     } 
     function getReturnList($start=null,$end=null,$branch=null,$type=null,$submit=null)
     {
         $this->db->select("*");      
	     $this->db->from('stock_return');
         if($start != '' && $start != null){
            $this->db->where('DATE(datereturned) >=', date("Y-m-d", strtotime($start)));
         }
         if($end != '' && $end != null){
            $this->db->where('DATE(datereturned) <=', date("Y-m-d", strtotime($end)));
         }
         if($branch != '' && $branch != null)
         {
            $this->db->where('branch_id', db_clean($branch));
         }
         if($type !='' && $type != null)
         {
            $this->db->where('type_id',id_clean($type));
         }
         if($submit != null && $submit=='export'){
            $this->db->where('activate',1);
         }
         $this->db->order_by("item");
         $query = $this->db->get();
         return $query->result();
     }  
     function acceptReturn($stockId,$branch)
     {
        $this->db->set('activate',1);
        $this->db->where('item_id', id_clean($stockId));
        $this->db->where('branch_id', $branch);
        $this->db->update('stock_return');
     }
     
 }